package net.jueb.frameWorkDemo.mvc4mod.mapper;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import net.jueb.frameWorkDemo.mvc4mod.mapper.Mybatis3_3_1_Mapper.User;

/**
 * 官方文档地址:http://www.mybatis.org/mybatis-3/zh/index.html
 * @author juebanlin
 */
public interface Mybatis3_SqlBuilder_Mapper {

	
	@SelectProvider(type = SqlBuilder.class, method = "buildGetUsersByName")
	List<User> getUsersByName(String name);

	@SelectProvider(type = SqlBuilder.class, method = "buildGetUsersByName2")
	List<User> getUsersByName2(@Param("name") String name, @Param("orderByColumn") String orderByColumn);

	
	public class SqlBuilder
	{
		
		public String buildGetUsersByName(final String name) {
		    return new SQL(){{
		      SELECT("*");
		      FROM("users");
		      if (name != null) {
		        WHERE("name like #{value} || '%'");
		      }
		      ORDER_BY("id");
		    }}.toString();
		  }
		
		// If not use @Param, you should be define same arguments with mapper method
		  public String buildGetUsersByName2(
		      final String name, final String orderByColumn) {
		    return new SQL(){{
		      SELECT("*");
		      FROM("users");
		      WHERE("name like #{name} || '%'");
		      ORDER_BY(orderByColumn);
		    }}.toString();
		  }

		  // If use @Param, you can define only arguments to be used
		  public String buildGetUsersByName2(@Param("orderByColumn") final String orderByColumn) {
		    return new SQL(){{
		      SELECT("*");
		      FROM("users");
		      WHERE("name like #{name} || '%'");
		      ORDER_BY(orderByColumn);
		    }}.toString();
		  }
		
		public String selectPersonSql() {
			  return new SQL() {{
			    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
			    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
			    FROM("PERSON P");
			    FROM("ACCOUNT A");
			    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
			    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
			    WHERE("P.ID = A.ID");
			    WHERE("P.FIRST_NAME like ?");
			    OR();
			    WHERE("P.LAST_NAME like ?");
			    GROUP_BY("P.ID");
			    HAVING("P.LAST_NAME like ?");
			    OR();
			    HAVING("P.FIRST_NAME like ?");
			    ORDER_BY("P.ID");
			    ORDER_BY("P.FULL_NAME");
			  }}.toString();
			}
		
		// Anonymous inner class
		public String deletePersonSql1() {
		  return new SQL() {{
		    DELETE_FROM("PERSON");
		    WHERE("ID = #{id}");
		  }}.toString();
		}

		// Builder / Fluent style
		public String insertPersonSql2() {
		  String sql = new SQL()
		    .INSERT_INTO("PERSON")
		    .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
		    .VALUES("LAST_NAME", "#{lastName}")
		    .toString();
		  return sql;
		}

		// With conditionals (note the final parameters, required for the anonymous inner class to access them)
		public String selectPersonLike(final String id, final String firstName, final String lastName) {
		  return new SQL() {{
		    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
		    FROM("PERSON P");
		    if (id != null) {
		      WHERE("P.ID like #{id}");
		    }
		    if (firstName != null) {
		      WHERE("P.FIRST_NAME like #{firstName}");
		    }
		    if (lastName != null) {
		      WHERE("P.LAST_NAME like #{lastName}");
		    }
		    ORDER_BY("P.LAST_NAME");
		  }}.toString();
		}

		public String deletePersonSql() {
		  return new SQL() {{
		    DELETE_FROM("PERSON");
		    WHERE("ID = #{id}");
		  }}.toString();
		}

		public String insertPersonSql() {
		  return new SQL() {{
		    INSERT_INTO("PERSON");
		    VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
		    VALUES("LAST_NAME", "#{lastName}");
		  }}.toString();
		}

		public String updatePersonSql() {
		  return new SQL() {{
		    UPDATE("PERSON");
		    SET("FIRST_NAME = #{firstName}");
		    WHERE("ID = #{id}");
		  }}.toString();
		}
	}
}
